﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class PreRegisterDAL
    {
        private const string SQL_SELECT_WS_PRE_REGISTER = "select PRE_REGISTER_ID as REGISTER_ID,PRE_CUSTOMER_ID as CUSTOMER_ID,PRE_CUSTOMER_NAME as CUSTOMER_NAME,PRE_MOBILE_NUMBER as MOBILE_NUMBER,PRE_TELEPHONE as TELEPHONE,PRE_EMAIL as EMAIL,PRE_QQ as QQ,PRE_MSN as MSN,PRE_TRAVEL_TYPE as TRAVEL_TYPE,PRE_DEPARTURE_CITY_ID as DEPARTURE_CITY_ID,PRE_ARRIVAL_CITY_ID as ARRIVAL_CITY_ID,PRE_AIRLINE_ID as AIRLINE_ID,date_format(PRE_DEPARTURE_DATE,'%Y-%m-%d') as DEPARTURE_DATE,date_format(PRE_RETURN_DATE,'%Y-%m-%d') as RETURN_DATE,date_format(PRE_PRINT_DATE,'%Y-%m-%d') as PRINT_DATE,PRE_ID_TYPE_ID as ID_TYPE_ID,PRE_ID_NUMBER as ID_NUMBER,date_format(PRE_EXPIRY_DATE,'%Y-%m-%d') as EXPIRY_DATE,date_format(PRE_BIRTHDAY,'%Y-%m-%d') as BIRTHDAY,PRE_VISA_TYPE as VISA_TYPE,PRE_TOTAL_PEOPLE as TOTAL_PEOPLE,PRE_DEAL_DESC as DEAL_DESC,PRE_REMARK as REMARK,PRE_FOLLOW_STAFF_ID as FOLLOW_STAFF_ID,PRE_REGISTER_TIME as REGISTER_TIME,PRE_TRANSFER_FLG as TRANSFER_FLG,PRE_RCRE_STAFF_ID as RCRE_STAFF_ID,date_format(PRE_RCRE_TIME,'%Y-%m-%d %H:%i:%s') as RCRE_TIME,PRE_LCHG_STAFF_ID as LCHG_STAFF_ID,date_format(PRE_LCHG_TIME,'%Y-%m-%d %H:%i:%s') as LCHG_TIME,(case PRE_TRAVEL_TYPE when 1 then '单程' when 2 then '往返' else '单程' end) as TRAVEL_TYPE_DESC,(select CIT_CITY_NAME from WS_CITY where CIT_CITY_ID=PRE_DEPARTURE_CITY_ID) as DEPARTURE_CITY_NAME,(select CIT_CITY_NAME from WS_CITY where CIT_CITY_ID=PRE_ARRIVAL_CITY_ID) as ARRIVAL_CITY_NAME,(select ALI_AIRLINE_NAME from WS_AIRLINE where ALI_AIRLINE_ID=PRE_AIRLINE_ID) as AIRLINE_NAME,(select IDT_TYPE_NAME from WS_ID_TYPE where IDT_TYPE_ID=PRE_ID_TYPE_ID) as ID_TYPE_NAME,(case PRE_VISA_TYPE when '01' then '旅游签证' when '02' then '旅游签证' when '03' then '探亲签证' else '旅游签证' end) as VISA_TYPE_DESC,(select STF_STAFF_NAME from WS_STAFF where STF_STAFF_ID=PRE_FOLLOW_STAFF_ID) as FOLLOW_STAFF_NAME,PRE_BOOKING_ID as BOOKING_ID,PRE_BOOKING_TYPE as BOOKING_TYPE,(case PRE_BOOKING_TYPE when 'LOC' then '现场预订' when 'TEL' then '电话预订' when 'WEB' then '网上预订' when 'SEO' then '推广预订' else '网上预订' end) as BOOKING_TYPE_DESC,PRE_WECHAT as WECHAT,insert(PRE_MOBILE_NUMBER,4,4,'****') as MOBILE_NUMBER_START from WS_PRE_REGISTER where 1=1 ";
        private const string SQL_INSERT_WS_PRE_REGISTER = "insert into WS_PRE_REGISTER(PRE_REGISTER_ID,PRE_CUSTOMER_ID,PRE_CUSTOMER_NAME,PRE_MOBILE_NUMBER,PRE_TELEPHONE,PRE_EMAIL,PRE_QQ,PRE_MSN,PRE_TRAVEL_TYPE,PRE_DEPARTURE_CITY_ID,PRE_ARRIVAL_CITY_ID,PRE_AIRLINE_ID,PRE_DEPARTURE_DATE,PRE_RETURN_DATE,PRE_PRINT_DATE,PRE_ID_TYPE_ID,PRE_ID_NUMBER,PRE_EXPIRY_DATE,PRE_BIRTHDAY,PRE_VISA_TYPE,PRE_TOTAL_PEOPLE,PRE_DEAL_DESC,PRE_REMARK,PRE_FOLLOW_STAFF_ID,PRE_REGISTER_TIME,PRE_TRANSFER_FLG,PRE_RCRE_STAFF_ID,PRE_RCRE_TIME,PRE_BOOKING_TYPE,PRE_WECHAT) values(@RegisterId,@CustomerId,@CustomerName,@MobileNumber,@Telephone,@EMail,@QQ,@MSN,@TravelType,@DepartureCityId,@ArrivalCityId,@AirlineId,@DepartureDate,@ReturnDate,@PrintDate,@IDTypeId,@IDNumber,@ExpiryDate,@Birthday,@VisaType,@TotalPeople,@DealDesc,@Remark,@FollowStaffId,@RegisterTime,@TransferFlag,@LastStaffId,@LastTime,@BookingType,@WeChat)";
        private const string SQL_UPDATE_WS_PRE_REGISTER = "update WS_PRE_REGISTER set PRE_CUSTOMER_NAME=@CustomerName,PRE_MOBILE_NUMBER=@MobileNumber,PRE_TELEPHONE=@Telephone,PRE_EMAIL=@EMail,PRE_QQ=@QQ,PRE_MSN=@MSN,PRE_TRAVEL_TYPE=@TravelType,PRE_DEPARTURE_CITY_ID=@DepartureCityId,PRE_ARRIVAL_CITY_ID=@ArrivalCityId,PRE_AIRLINE_ID=@AirlineId,PRE_DEPARTURE_DATE=@DepartureDate,PRE_RETURN_DATE=@ReturnDate,PRE_PRINT_DATE=@PrintDate,PRE_ID_TYPE_ID=@IDTypeId,PRE_ID_NUMBER=@IDNumber,PRE_EXPIRY_DATE=@ExpiryDate,PRE_BIRTHDAY=@Birthday,PRE_VISA_TYPE=@VisaType,PRE_TOTAL_PEOPLE=@TotalPeople,PRE_DEAL_DESC=@DealDesc,PRE_REMARK=@Remark,PRE_FOLLOW_STAFF_ID=@FollowStaffId,PRE_LCHG_STAFF_ID=@LastStaffId,PRE_LCHG_TIME=@LastTime,PRE_BOOKING_TYPE=@BookingType,PRE_WECHAT=@WeChat where 1=1 ";
        private const string SQL_UPDATE_TRANSFER_BOOKING = "update WS_PRE_REGISTER set PRE_TRANSFER_FLG=@TransferFlag,PRE_BOOKING_ID=@BookingId,PRE_LCHG_STAFF_ID=@LastStaffId,PRE_LCHG_TIME=@LastTime where 1=1 ";
        private const string SQL_SELECT_LAST_STAFF_ID = "select PRE_FOLLOW_STAFF_ID from WS_PRE_REGISTER where 1=1 ";

        private const string SQL_WHERE_REGISTER_ID = " and PRE_REGISTER_ID=@RegisterId ";
        private const string SQL_WHERE_FOLLOW_STAFF_ID = " and PRE_FOLLOW_STAFF_ID=@FollowStaffId ";
        private const string SQL_WHERE_CUSTOMER_NAME = " and PRE_CUSTOMER_NAME=@CustomerName ";
        private const string SQL_WHERE_CUSTOMER_NAME_LIKE = " and PRE_CUSTOMER_NAME like @CustomerName ";
        private const string SQL_WHERE_MOBILE_NUMBER = " and PRE_MOBILE_NUMBER=@MobileNumber ";
        private const string SQL_WHERE_MOBILE_NUMBER_LIKE = " and PRE_MOBILE_NUMBER like @MobileNumber ";
        private const string SQL_WHERE_REGISTER_TIME = " and PRE_REGISTER_TIME between @StartTime and @EndTime ";
        private const string SQL_WHERE_CUSTOMER_ID = " and PRE_CUSTOMER_ID=@CustomerId ";

        private const string SQL_ORDER_BY_REGISTER_TIME = " order by REGISTER_TIME ";
        private const string SQL_ORDER_BY_REGISTER_TIME_DESC = "order by REGISTER_TIME desc";

        private const string PARM_REGISTER_ID = "@RegisterId";
        private const string PARM_CUSTOMER_ID = "@CustomerId";
        private const string PARM_CUSTOMER_NAME = "@CustomerName";
        private const string PARM_BIRTHDAY = "@Birthday";
        private const string PARM_MOBILE_NUMBER = "@MobileNumber";
        private const string PARM_TELEPHONE = "@Telephone";
        private const string PARM_EMAIL = "@EMail";
        private const string PARM_QQ = "@QQ";
        private const string PARM_MSN = "@MSN";
        private const string PARM_TRAVEL_TYPE = "@TravelType";
        private const string PARM_DEPARTURE_CITY_ID = "@DepartureCityId";
        private const string PARM_ARRIVAL_CITY_ID = "@ArrivalCityId";
        private const string PARM_AIRLINE_ID = "@AirlineId";
        private const string PARM_DEPARTURE_DATE = "@DepartureDate";
        private const string PARM_RETURN_DATE = "@ReturnDate";
        private const string PARM_PRINT_DATE = "@PrintDate";
        private const string PARM_ID_TYPE_ID = "@IDTypeId";
        private const string PARM_ID_NUMBER = "@IDNumber";
        private const string PARM_EXPIRY_DATE = "@ExpiryDate";
        private const string PARM_VISA_TYPE = "@VisaType";
        private const string PARM_TOTAL_PEOPLE = "@TotalPeople";
        private const string PARM_DEAL_DESC = "@DealDesc";
        private const string PARM_REMARK = "@Remark";
        private const string PARM_FOLLOW_STAFF_ID = "@FollowStaffId";
        private const string PARM_REGISTER_TIME = "@RegisterTime";
        private const string PARM_TRANSFER_FLG = "@TransferFlag";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_START_TIME = "@StartTime";
        private const string PARM_END_TIME = "@EndTime";
        private const string PARM_BOOKING_ID = "@BookingId";
        private const string PARM_BOOKING_TYPE = "@BookingType";
        private const string PARM_WECHAT = "@WeChat";

        Nullable<DateTime> NullDateTime = null;
        private static PreRegisterDAL instance = new PreRegisterDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static PreRegisterDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(QueryPreRegisterModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_PRE_REGISTER);
            List<MySqlParameter> parms = new List<MySqlParameter>();

            if (model != null)
            {
                if (model.QueryRegisterId)
                {
                    sbSQL.Append(SQL_WHERE_REGISTER_ID);
                    parms.Add(new MySqlParameter(PARM_REGISTER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RegisterId });
                }
                if (model.QueryStaffId)
                {
                    sbSQL.Append(SQL_WHERE_FOLLOW_STAFF_ID);
                    parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
                }
                if (model.QueryName)
                {
                    if (model.FuzzyName)
                    {
                        sbSQL.Append(SQL_WHERE_CUSTOMER_NAME_LIKE);
                        parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.Name) });
                    }
                    else
                    {
                        sbSQL.Append(SQL_WHERE_CUSTOMER_NAME);
                        parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
                    }
                }
                if (model.QueryMobileNumber)
                {
                    if (model.FuzzyMobileNumber)
                    {
                        sbSQL.Append(SQL_WHERE_MOBILE_NUMBER_LIKE);
                        parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.MobileNumber) });
                    }
                    else
                    {
                        sbSQL.Append(SQL_WHERE_MOBILE_NUMBER);
                        parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
                    }
                }
                if (model.QueryRegisterTime)
                {
                    sbSQL.Append(SQL_WHERE_REGISTER_TIME);
                    parms.Add(new MySqlParameter(PARM_START_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.StartTime });
                    parms.Add(new MySqlParameter(PARM_END_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.EndTime });
                }
            }
            sbSQL.Append(SQL_ORDER_BY_REGISTER_TIME);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public PreRegisterModel Get(string registerId)
        {
            using (DataSet ds = this.GetDataSet(new QueryPreRegisterModel() { QueryRegisterId = true, RegisterId = registerId }))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new PreRegisterModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        CustomerId = Convert.ToString(t.ItemArray[1]),
                        CustomerName = Convert.ToString(t.ItemArray[2]),
                        MobileNumber = Convert.ToString(t.ItemArray[3]),
                        Telephone = t.IsNull(4) ? string.Empty : Convert.ToString(t.ItemArray[4]),
                        EMail = t.IsNull(5) ? string.Empty : Convert.ToString(t.ItemArray[5]),
                        QQ = t.IsNull(6) ? string.Empty : Convert.ToString(t.ItemArray[6]),
                        Msn = t.IsNull(7) ? string.Empty : Convert.ToString(t.ItemArray[7]),
                        TravelType = Convert.ToInt32(t.ItemArray[8]),
                        DepartureCityId = Convert.ToString(t.ItemArray[9]),
                        ArrivalCityId = Convert.ToString(t.ItemArray[10]),
                        AirlineId = Convert.ToString(t.ItemArray[11]),
                        DepartureDate = t.IsNull(12) ? NullDateTime : Convert.ToDateTime(t.ItemArray[12]),
                        ReturnDate = t.IsNull(13) ? NullDateTime : Convert.ToDateTime(t.ItemArray[13]),
                        PrintDate = t.IsNull(14) ? NullDateTime : Convert.ToDateTime(t.ItemArray[14]),
                        IDTypeId = t.IsNull(15) ? string.Empty : Convert.ToString(t.ItemArray[15]),
                        IDNumber = t.IsNull(16) ? string.Empty : Convert.ToString(t.ItemArray[16]),
                        ExpiryDate = t.IsNull(17) ? NullDateTime : Convert.ToDateTime(t.ItemArray[17]),
                        Birthday = t.IsNull(18) ? NullDateTime : Convert.ToDateTime(t.ItemArray[18]),
                        VisaType = Convert.ToString(t.ItemArray[19]),
                        TotalPeople = Convert.ToInt32(t.ItemArray[20]),
                        DealDesc = Convert.ToString(t.ItemArray[21]),
                        Remark = t.IsNull(22) ? string.Empty : Convert.ToString(t.ItemArray[22]),
                        FollowStaffId = t.IsNull(23) ? string.Empty : Convert.ToString(t.ItemArray[23]),
                        RegisterTime = Convert.ToDateTime(t.ItemArray[24]),
                        Transferred = string.Equals(Convert.ToString(t.ItemArray[25]), "Y"),
                        CreatedStaffId = Convert.ToString(t.ItemArray[26]),
                        CreatedDate = Convert.ToDateTime(t.ItemArray[27]),
                        LastModifiedStaffId = t.IsNull(28) ? string.Empty : Convert.ToString(t.ItemArray[28]),
                        LastModifiedDate = t.IsNull(29) ? NullDateTime : Convert.ToDateTime(t.ItemArray[29]),
                        TravelTypeDesc = Convert.ToString(t.ItemArray[30]),
                        DepartureCity = Convert.ToString(t.ItemArray[31]),
                        ArrivalCity = Convert.ToString(t.ItemArray[32]),
                        Airline = t.IsNull(33) ? string.Empty : Convert.ToString(t.ItemArray[33]),
                        IDTypeName = t.IsNull(34) ? string.Empty : Convert.ToString(t.ItemArray[34]),
                        VisaTypeDesc = Convert.ToString(t.ItemArray[35]),
                        FollowStaffName = t.IsNull(36) ? string.Empty : Convert.ToString(t.ItemArray[36]),
                        BookingId = t.IsNull(37) ? string.Empty : Convert.ToString(t.ItemArray[37]),
                        BookingType = t.IsNull(38) ? string.Empty : Convert.ToString(t.ItemArray[38]),
                        BookingTypeDesc = t.IsNull(39) ? string.Empty : Convert.ToString(t.ItemArray[39]),
                        WeChat = t.IsNull(40) ? string.Empty : Convert.ToString(t.ItemArray[40]),
                    };
                }
                return null;
            }
        }
        public string GetLastStaffId(string customerId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_LAST_STAFF_ID);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);
            sbSQL.Append(SQL_ORDER_BY_REGISTER_TIME_DESC);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = customerId });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, MySQLHelper.Instance.GetSQLPaging(sbSQL.ToString(), 1, 1), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return Convert.ToString(t.ItemArray[0]);
                }
                return string.Empty;
            }
        }
        public int Insert(DbTransaction trans, PreRegisterModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_REGISTER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerName });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_TELEPHONE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Telephone });
            parms.Add(new MySqlParameter(PARM_EMAIL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EMail });
            parms.Add(new MySqlParameter(PARM_QQ, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.QQ });
            parms.Add(new MySqlParameter(PARM_MSN, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Msn });
            parms.Add(new MySqlParameter(PARM_TRAVEL_TYPE, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.TravelType });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DepartureCityId });
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ArrivalCityId });
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirlineId });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.DepartureDate });
            parms.Add(new MySqlParameter(PARM_RETURN_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.ReturnDate });
            parms.Add(new MySqlParameter(PARM_PRINT_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.PrintDate });
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDTypeId });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });
            parms.Add(new MySqlParameter(PARM_EXPIRY_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.ExpiryDate });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_VISA_TYPE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.VisaType });
            parms.Add(new MySqlParameter(PARM_TOTAL_PEOPLE, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.TotalPeople });
            parms.Add(new MySqlParameter(PARM_DEAL_DESC, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DealDesc });
            parms.Add(new MySqlParameter(PARM_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Remark });
            parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FollowStaffId });
            parms.Add(new MySqlParameter(PARM_REGISTER_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.RegisterTime });
            parms.Add(new MySqlParameter(PARM_TRANSFER_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Transferred ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_BOOKING_TYPE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingType });
            parms.Add(new MySqlParameter(PARM_WECHAT, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.WeChat });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_PRE_REGISTER, parms.ToArray());
        }
        public int Update(DbTransaction trans, PreRegisterModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_PRE_REGISTER);
            sbSQL.Append(SQL_WHERE_REGISTER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_REGISTER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerName });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_TELEPHONE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Telephone });
            parms.Add(new MySqlParameter(PARM_EMAIL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EMail });
            parms.Add(new MySqlParameter(PARM_QQ, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.QQ });
            parms.Add(new MySqlParameter(PARM_MSN, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Msn });
            parms.Add(new MySqlParameter(PARM_TRAVEL_TYPE, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.TravelType });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DepartureCityId });
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ArrivalCityId });
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirlineId });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.DepartureDate });
            parms.Add(new MySqlParameter(PARM_RETURN_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.ReturnDate });
            parms.Add(new MySqlParameter(PARM_PRINT_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.PrintDate });
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDTypeId });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });
            parms.Add(new MySqlParameter(PARM_EXPIRY_DATE, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.ExpiryDate });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_VISA_TYPE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.VisaType });
            parms.Add(new MySqlParameter(PARM_TOTAL_PEOPLE, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.TotalPeople });
            parms.Add(new MySqlParameter(PARM_DEAL_DESC, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DealDesc });
            parms.Add(new MySqlParameter(PARM_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Remark });
            parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FollowStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_BOOKING_TYPE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingType });
            parms.Add(new MySqlParameter(PARM_WECHAT, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.WeChat });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int TransferBooking(DbTransaction trans, PreRegisterModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_TRANSFER_BOOKING);
            sbSQL.Append(SQL_WHERE_REGISTER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_REGISTER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_TRANSFER_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Transferred ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingId });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
